﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.SERVICE.DAL;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class Command
    {
        Common common = new Common();
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        public List<hrm_ps_commands> Select(hrm_ps_commands filter = null, int? cmd_type_seq = null, int? cmd_group_seq = null)
        {
            var result = new List<hrm_ps_commands>();
            if (filter == null) filter = new hrm_ps_commands();
            List<hrm_ps_employee_types> employeeTypeses = null;
            List<hrm_mp_job_sites> jobSiteses = null;
            List<hrm_ps_employees> employeeses = null;
            List<hrm_dc_penalty> penalties = null;
            try
            {
                employeeTypeses = new Employee().GetHrmPsEmployeeTypes(null);
                jobSiteses = new Employee().GetHrmMpJobSites(null, null, null, null, null, null, null, false, false, false, false, false, false, false, false, false, false);
                employeeses = new Employee().GetHrmPsEmployee(null,null,null,null,null,null,null,null,false,false,false,false,false,false,false,false,false,false,false,false,false,false);
                penalties = null;
            } catch
            {
                
            }

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                int i = 0;
                dbManager.AddParameters(i++, "@deletedStatus", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@cmd_seq", filter.cmd_seq);
                dbManager.AddParameters(i++, "@cmd_type_seq", filter.cmd_type_seq);

                string strSql = @"SELECT c.cmd_seq,
  c.cmd_flag,
  c.cmd_draft_no,
  c.cmd_name,
  c.cmd_descr,
  c.cmd_ref_no,
  c.cmd_type_seq,
  c.emp_type_seq,
  c.job_site_seq,
  c.penalty_seq,
  c.cmd_status,
  c.approved_seq,
  c.approved_name,
  c.approved_positions,
  c.approved_date,
  c.cmd_rpt_date,
  c.inv_type_seq,
  c.confirm,
  c.rpt_header,
  c.remarks, 
  c.record_status as c_record_status,
  t.cmd_type_code,
  t.cmd_type_name,
  t.cmd_type_name_short,
  t.cmd_group_seq,
  t.rpt_format,
  t.remarks,
  t.record_status,
  t.created_by,
  t.created_when,
  t.updated_by,
  t.updated_when,
  t.rpt_header, 
  t.record_status as t_record_status,
  g.cmd_group_name,
  g.record_status as g_record_status
  
FROM hrm_ps_commands c
INNER JOIN hrm_ps_command_types t ON c.cmd_type_seq = t.cmd_type_seq
INNER JOIN hrm_ps_command_groups g ON t.cmd_group_seq = g.cmd_group_seq
WHERE 
c.record_status <> @deletedStatus
AND t.record_status <> @deletedStatus
AND g.record_status <> @deletedStatus
 ";

                if (!common.IsIntNullOrZero(filter.cmd_seq))
                {
                    strSql += " and c.cmd_seq = @cmd_seq ";
                }
                if (!common.IsIntNullOrZero(filter.cmd_type_seq))
                {
                    strSql += " and c.cmd_type_seq = @cmd_type_seq ";
                }
                if (!common.IsIntNullOrZero(cmd_group_seq))
                {
                    strSql += " and t.cmd_group_seq = @cmd_group_seq ";
                } 

                DataRowCollection drc = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0].Rows;
                foreach (DataRow dr in drc)
                {
                    var empType = employeeTypeses != null ? (from t in employeeTypeses where t.emp_type_seq == common.ConvertToNullableInt(dr["emp_type_seq"]) select t).FirstOrDefault() : null;
                    var jobSite = jobSiteses != null ? (from t in jobSiteses where t.job_site_seq == common.ConvertToNullableInt(dr["job_site_seq"]) select t).FirstOrDefault() : null;
                    var approver = employeeses != null ? (from t in employeeses where t.emp_seq == common.ConvertToNullableInt(dr["approved_seq"]) select t).FirstOrDefault() : null;
                    var cmdType = new hrm_ps_command_types
                        {
                            cmd_type_seq = Convert.ToInt32(dr["cmd_type_seq"]),
                            cmd_type_code = Convert.ToString(dr["cmd_type_code"]),
                            cmd_type_name = Convert.ToString(dr["cmd_type_name"]),
                            cmd_type_name_short = Convert.ToString(dr["cmd_type_name_short"]),
                            cmd_group_seq = common.ConvertToNullableInt(dr["cmd_group_seq"]),
                            rpt_format = Convert.ToString(dr["rpt_format"]),
                            remarks = Convert.ToString(dr["remarks"]),
                            record_status = Convert.ToString(dr["record_status"]),
                            rpt_header = Convert.ToString(dr["rpt_header"]),
                            hrm_ps_command_groups = new hrm_ps_command_groups
                                {
                                    cmd_group_seq = Convert.ToInt32(dr["cmd_type_seq"]),
                                    cmd_group_name = Convert.ToString(dr["cmd_group_name"]),
                                }
                        };
                    result.Add(new hrm_ps_commands
                    {
                        cmd_seq = Convert.ToInt32(dr["cmd_seq"]),
                        cmd_type_seq = Convert.ToInt32(dr["cmd_type_seq"]),
                        cmd_flag = Convert.ToString(dr["cmd_flag"]),
                        cmd_draft_no = Convert.ToString(dr["cmd_draft_no"]),
                        cmd_name = Convert.ToString(dr["cmd_name"]),
                        cmd_descr = Convert.ToString(dr["cmd_descr"]),
                        cmd_ref_no = Convert.ToString(dr["cmd_ref_no"]),
                        emp_type_seq = common.ConvertToNullableInt(dr["emp_type_seq"]),
                        job_site_seq = common.ConvertToNullableInt(dr["job_site_seq"]),
                        penalty_seq = common.ConvertToNullableInt(dr["penalty_seq"]),
                        cmd_status = Convert.ToString(dr["cmd_status"]),
                        approved_seq = common.ConvertToNullableInt(dr["approved_seq"]),
                        approved_name = Convert.ToString(dr["approved_name"]),
                        approved_positions = Convert.ToString(dr["approved_positions"]),
                        approved_date = common.ConvertToNullableDateTime(dr["approved_date"]),
                        cmd_rpt_date = Convert.ToString(dr["cmd_rpt_date"]),
                        inv_type_seq = common.ConvertToNullableInt(dr["inv_type_seq"]),
                        confirm = common.ConvertToNullableInt(dr["confirm"]),
                        rpt_header = Convert.ToString(dr["rpt_header"]),
                        remarks = Convert.ToString(dr["remarks"]),
                        record_status = Convert.ToString(dr["c_record_status"]),
                        hrm_ps_command_types = cmdType,
                        hrm_mp_job_sites = jobSite,
                        hrm_ps_employee_types = empType,
                        approver = approver
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            return result;
        }


        public void UpdateDetail(int cmd_dtls_seq, string updateTo, int updateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                int i = 0;
                dbManager.AddParameters(i++, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(i++, "@record_status", updateTo);
                dbManager.AddParameters(i++, "@updated_by", updateBy);

                string strSql = @"UPDATE hrm_ps_command_dtls
SET record_status = @record_status,
updated_by = @updated_by,
updated_when = NOW()
WHERE cmd_dtls_seq = @cmd_dtls_seq;
 ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }
    }
}
